| springboot+mybatis调用oracle存储过程 | 您所在的位置：网站首页 › spring boot 调用存储过程 › springboot+mybatis调用oracle存储过程 |
springboot+mybatis调用oracle存储过程
|
1 存储过程参数为VARCHAR 代码逻辑：controller层定义实体类对象entity，并entity.set给存储过程的输入参数赋值，把赋值后的实体类通过service层传到dao层，然后通过dao层调用存储过程 1-1 存储过程参数  
IN_STR IN VARCHAR,
OUT_STR OUT VARCHAR
View Code
1-2 controller层
@RequestMapping("/TESTPRO")
@ResponseBody
public String TESTPRO() {
// 执行oracle存储过程
// 实体类中把输入参数输出参数都写上
//少写参数会报错：(wrong number or types of arguments in call to '存储过程')
HelloProEntity entity = new HelloProEntity();
entity.setIN_STR("1");// 配置输入参数
controllerService.GetTestPro(entity);// 把带输入参数的实体传到Service层，Service层调用Dao层，Dao层寻找实体对应的sql
try {
String str = entity.getOUT_STR();
return str;
} catch (Exception e) {
return "null";
}
}
View Code
1-3 service层
public HelloProEntity GetTestPro(HelloProEntity testproentity) {
return dao.GetTestPro(testproentity);
}
View Code
1-4 dao层
public HelloProEntity GetTestPro(HelloProEntity testproentity);
View Code
1-5 实体类
public class HelloProEntity implements Serializable {
private static final long serialVersionUID = -6556793741331167103L;
private String IN_STR;
private String OUT_STR;
public String getIN_STR() {
return IN_STR;
}
public void setIN_STR(String iN_STR) {
IN_STR = iN_STR;
}
public String getOUT_STR() {
return OUT_STR;
}
public void setOUT_STR(String oUT_STR) {
OUT_STR = oUT_STR;
}
}
View Code
1-6 mapper
{call JCG_TEST(
#{IN_STR,mode=IN,jdbcType=VARCHAR},
#{OUT_STR,mode=OUT,jdbcType=VARCHAR}
)}
]]>
View Code
2 存储过程参数为DATE 基于第1点的配置，service层与dao层不需要改动，改动controller、实体类、mapper 主要注意的是：存储过程输入参数输出参数为DATE格式时，mapper定义jdbcType为DATE，实体类要用Timestamp格式定义属性，否则时分秒会变成00:00:00。 输出得到时间戳后，再用new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(entity.getM_GROUP_END())转成字符串格式的时间。 2-1 存储过程参数
M_DATETIME IN DATE,
M_SEGMENT_BEGIN OUT DATE,
M_SEGMENT_END OUT DATE,
M_GROUP_BEGIN OUT DATE,
M_GROUP_END OUT DATE,
M_SEGMENT_NO OUT INTEGER,
M_GROUP_CODE OUT VARCHAR2,
RES OUT VARCHAR2
View Code
2-1 controller层
@RequestMapping("/GETPRO")
@ResponseBody
public PGetTimeSlotInfoEntity TESTPRO() throws SQLException {
PGetTimeSlotInfoEntity entity = new PGetTimeSlotInfoEntity();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = df.format(new Date());
Timestamp ts = Timestamp.valueOf(time);//获取当前时间的时间戳
entity.setM_DATETIME(ts);//以时间戳给输入的DATE参数赋值
service.GetPro(entity);
return entity;
}
View Code
2-2 实体类
public class PGetTimeSlotInfoEntity implements Serializable {
private static final long serialVersionUID = -6556793741331167103L;
private Timestamp M_DATETIME;
private Timestamp M_SEGMENT_BEGIN;// 这里不用Date，应为传不了时分秒
private Timestamp M_SEGMENT_END;
private Timestamp M_GROUP_BEGIN;
private Timestamp M_GROUP_END;
private String M_SEGMENT_NO;
private String M_GROUP_CODE;
private String RES;
public Timestamp getM_DATETIME() {
return M_DATETIME;
}
public void setM_DATETIME(Timestamp m_DATETIME) {
M_DATETIME = m_DATETIME;
}
public Date getM_SEGMENT_BEGIN() {
return M_SEGMENT_BEGIN;
}
public void setM_SEGMENT_BEGIN(Timestamp m_SEGMENT_BEGIN) {
M_SEGMENT_BEGIN = m_SEGMENT_BEGIN;
}
public Date getM_SEGMENT_END() {
return M_SEGMENT_END;
}
public void setM_SEGMENT_END(Timestamp m_SEGMENT_END) {
M_SEGMENT_END = m_SEGMENT_END;
}
public Timestamp getM_GROUP_BEGIN() {
return M_GROUP_BEGIN;
}
public void setM_GROUP_BEGIN(Timestamp m_GROUP_BEGIN) {
M_GROUP_BEGIN = m_GROUP_BEGIN;
}
public Timestamp getM_GROUP_END() {
return M_GROUP_END;
}
public void setM_GROUP_END(Timestamp m_GROUP_END) {
M_GROUP_END = m_GROUP_END;
}
public String getM_SEGMENT_NO() {
return M_SEGMENT_NO;
}
public void setM_SEGMENT_NO(String m_SEGMENT_NO) {
M_SEGMENT_NO = m_SEGMENT_NO;
}
public String getM_GROUP_CODE() {
return M_GROUP_CODE;
}
public void setM_GROUP_CODE(String m_GROUP_CODE) {
M_GROUP_CODE = m_GROUP_CODE;
}
public String getRES() {
return RES;
}
public void setRES(String rES) {
RES = rES;
}
}
View Code
2-3 mapper
{call P_GET_TIME_SLOT_INFO(
#{M_DATETIME,mode=IN,jdbcType=DATE},
#{M_SEGMENT_BEGIN,mode=OUT,jdbcType=DATE},
#{M_SEGMENT_END,mode=OUT,jdbcType=DATE},
#{M_GROUP_BEGIN,mode=OUT,jdbcType=DATE},
#{M_GROUP_END,mode=OUT,jdbcType=DATE},
#{M_SEGMENT_NO,mode=OUT,jdbcType=INTEGER},
#{M_GROUP_CODE,mode=OUT,jdbcType=VARCHAR},
#{RES,mode=OUT,jdbcType=VARCHAR}
)}
]]>
View Code
|
【本文地址】
公司简介
联系我们